package cn.edu.yzu.scholarship.kit;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelKit {
	
	
	/**
	 * 导出Excel文件
	 * @param title 标题
	 * @param columns 数据中key
	 * @param headers 表头
	 * @param data 数据
	 * @param os 输出流
	 */
	public static void exportExcel(
			String title, String sheetName,String[] columns,String[] headers,List<Map<String,Object>> data,OutputStream os) {
		exportExcel(title, sheetName, columns, headers, data, null, os);
	}
	
	public static void exportExcel(
			String title, String sheetName,String[] columns,String[] headers,List<Map<String,Object>> data,List<String> footer,OutputStream os) {
		
		
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet(sheetName);
		StyleManager wbI = new StyleManager(wb);
		
		buildTitle(title,sheet,columns.length,wbI.getTitleStyle());
		buildHeader(sheet,headers,wbI.getHeaderStyle());
		buildData(sheet,columns,data,wbI);
		if(footer != null) builderFooter(sheet,data.size() + 2,columns.length, footer, wbI.getFootStyle());
		
		autoSizeColumn(sheet,columns.length);
		
		doExportExcel(wb,os);
	}

	private static void autoSizeColumn(Sheet sheet, int length) {
		for(int i = 0; i < length; i++) {
			sheet.autoSizeColumn(i);
			sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 3 / 2);//fix 中文列宽不足
		}
	}

	private static void doExportExcel(Workbook wb, OutputStream os) {
		try {
			wb.write(os);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}

	private static void buildTitle(String title,Sheet sheet, int columnLength,CellStyle titleStyle) {
		Row row = sheet.createRow(0);//设置标题并合并单元格
		row.createCell(0).setCellValue(title);
		row.setHeight((short)400);
		CellRangeAddress region = new CellRangeAddress(0,0,0,columnLength - 1);
		sheet.addMergedRegion(region);
		
		for(int i = 0; i < columnLength; i++) {//设置标题样式
			Cell cell = row.getCell(i);
            if(cell ==null ){
            	cell = row.createCell(i);
            }
            cell.setCellStyle(titleStyle);
		}
	}
	
	private static void buildHeader(Sheet sheet, String[] headers, CellStyle headerStyle) {
		Row row = sheet.createRow(1);//第二行为表头
		for(int i = 0; i < headers.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(headerStyle);
			cell.setCellValue(headers[i]);
		}
	}
	
	private static void buildData(Sheet sheet, String[] columns, List<Map<String,Object>> data,StyleManager wbI) {
		for(int i = 0; i < data.size(); i++) {
			Row _row = sheet.createRow(i + 2);//从第三行起，是数据列
			for(int j = 0; j < columns.length; j++) {
				Cell cell = _row.createCell(j);
				cell.setCellStyle(createStyle(columns[j], wbI));
				cell.setCellValue(String.valueOf(ObjectUtils.defaultIfNull(data.get(i).get(columns[j]),"")));
			}
		}
	}
	

	private static CellStyle createStyle(String column, StyleManager wbI) {
		return "scholarshipMoney".equals(column) ?  wbI.getMoneyStyle() : wbI.getBaseStyle();//金额特殊处理
	}

	private static void builderFooter(Sheet sheet, int startRow, int length, List<String> footer, CellStyle footStyle) {
		for(int i = 0; i < footer.size(); i++) {
			CellRangeAddress region = new CellRangeAddress(startRow + i,startRow + i,0,length - 1);
			sheet.addMergedRegion(region);
			
			Row row = sheet.createRow(startRow + i);//设置内容并合并单元格
			row.createCell(0).setCellValue(footer.get(i));
			row.setHeight((short)400);
			
			for(int j = 0; j < length; j ++) {//设置尾部样式
				Cell cell = row.getCell(j);
	            if(cell ==null ) cell = row.createCell(j);
	            cell.setCellStyle(footStyle);
			}
		}
	}
	
	private static class StyleManager {
		private Workbook wb = null;
		
		private CellStyle titleStyle;
		private CellStyle headerStyle;
		private CellStyle baseStyle;
		private CellStyle footStyle;
		private CellStyle moneyStyle;
		
		public StyleManager(Workbook wb) {
			this.wb = wb;
			init();
		}

		public CellStyle getFootStyle() {
			return footStyle;
		}

		private void init() {
			initStyle();
		}

		private void initStyle() {
			initBaseStyle();
			initTitleStyle();
			initHeaderStyle();
			initFootStyle();
			initMoneyStyle();
		}

		private void initMoneyStyle() {
			moneyStyle = wb.createCellStyle();
			moneyStyle.cloneStyleFrom(baseStyle);
			moneyStyle.setAlignment(CellStyle.ALIGN_RIGHT);
			moneyStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		}

		private void initFootStyle() {
			footStyle = wb.createCellStyle();//表尾样式
			footStyle.cloneStyleFrom(titleStyle);
		}

		private void initHeaderStyle() {
			headerStyle = wb.createCellStyle();//表头样式
			headerStyle.cloneStyleFrom(titleStyle);
		}

		private void initTitleStyle() {
			titleStyle = wb.createCellStyle();//标题样式
			titleStyle.cloneStyleFrom(baseStyle);
			titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
			titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			Font font = wb.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			titleStyle.setFont(font);
		}

		private void initBaseStyle() {
			baseStyle = wb.createCellStyle();//表格样式
			baseStyle.setBorderBottom(CellStyle.BORDER_THIN);
			baseStyle.setBorderTop(CellStyle.BORDER_THIN);
			baseStyle.setBorderLeft(CellStyle.BORDER_THIN);
			baseStyle.setBorderRight(CellStyle.BORDER_THIN);
		}

		public CellStyle getTitleStyle() {
			return titleStyle;
		}

		public CellStyle getHeaderStyle() {
			return headerStyle;
		}

		public CellStyle getBaseStyle() {
			return baseStyle;
		}

		public CellStyle getMoneyStyle() {
			return moneyStyle;
		}
	}
	
	
	public static List<String[]> getData(File file) {
		List<String[]> result = new ArrayList<String[]>();
		try {
			Workbook wb = new HSSFWorkbook(new FileInputStream(file));
			Sheet sheet = wb.getSheetAt(0);
			for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) {
				Row row = sheet.getRow(i);
				String[] data = new String[row.getLastCellNum() - row.getFirstCellNum()];
				for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
					data[j - row.getFirstCellNum()] = getCellValue(row.getCell(j));
				}
				result.add(data);
			}
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		
		return result;
	}
	
	private static String getCellValue(Cell cell){  
        String str = null;  
        switch (cell.getCellType()) {  
        case Cell.CELL_TYPE_BLANK:  
            str = ""; break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            str = String.valueOf(cell.getBooleanCellValue()); break;  
        case Cell.CELL_TYPE_FORMULA:  
            str = String.valueOf(cell.getCellFormula()); break;  
        case Cell.CELL_TYPE_NUMERIC:  
            str = String.format("%.0f",cell.getNumericCellValue()); break;  
        case Cell.CELL_TYPE_STRING:  
            str = cell.getStringCellValue(); break;  
        default:  
            str = null;  
            break;  
        }  
        return str;  
    }  
}
